#!/usr/bin/env ts-node

/**
 * 数据库初始化脚本
 * 创建所有必要的数据库表和初始数据
 */

import { pool } from '../config/database';
import { testRedisConnection } from '../config/redis';

async function createTables() {
  console.log('🗄️  创建数据库表...');

  const client = await pool.connect();
  
  try {
    // 开始事务
    await client.query('BEGIN');

    // 1. 创建文件夹表
    await client.query(`
      CREATE TABLE IF NOT EXISTS folders (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        name VARCHAR(255) NOT NULL,
        parent_id UUID REFERENCES folders(id) ON DELETE CASCADE,
        created_by VARCHAR(255),
        created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
        UNIQUE(name, parent_id)
      )
    `);

    // 2. 创建脚本表
    await client.query(`
      CREATE TABLE IF NOT EXISTS scripts (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        name VARCHAR(255) NOT NULL,
        content TEXT,
        description TEXT,
        folder_id UUID REFERENCES folders(id) ON DELETE SET NULL,
        tags TEXT[],
        created_by VARCHAR(255),
        created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
        UNIQUE(name, folder_id)
      )
    `);

    // 3. 创建版本表
    await client.query(`
      CREATE TABLE IF NOT EXISTS versions (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        script_id UUID NOT NULL REFERENCES scripts(id) ON DELETE CASCADE,
        version VARCHAR(50) NOT NULL,
        content TEXT NOT NULL,
        content_hash VARCHAR(64) NOT NULL,
        message TEXT,
        author VARCHAR(255),
        parent_version UUID REFERENCES versions(id),
        created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
        UNIQUE(script_id, version)
      )
    `);

    // 4. 创建数据库连接表
    await client.query(`
      CREATE TABLE IF NOT EXISTS connections (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        name VARCHAR(255) NOT NULL UNIQUE,
        host VARCHAR(255) NOT NULL,
        port INTEGER NOT NULL DEFAULT 9030,
        username VARCHAR(255) NOT NULL,
        password_encrypted TEXT NOT NULL,
        database_name VARCHAR(255),
        is_active BOOLEAN DEFAULT true,
        created_by VARCHAR(255),
        created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
      )
    `);

    // 5. 创建执行记录表
    await client.query(`
      CREATE TABLE IF NOT EXISTS executions (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        script_id UUID NOT NULL REFERENCES scripts(id) ON DELETE CASCADE,
        connection_id UUID NOT NULL REFERENCES connections(id) ON DELETE CASCADE,
        status VARCHAR(50) NOT NULL DEFAULT 'running',
        start_time TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
        end_time TIMESTAMP WITH TIME ZONE,
        duration_ms INTEGER,
        rows_affected INTEGER,
        error_message TEXT,
        execution_plan TEXT,
        result_preview JSONB,
        executed_by VARCHAR(255),
        created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
      )
    `);

    // 6. 创建索引
    await client.query(`
      CREATE INDEX IF NOT EXISTS idx_folders_parent_id ON folders(parent_id);
      CREATE INDEX IF NOT EXISTS idx_scripts_folder_id ON scripts(folder_id);
      CREATE INDEX IF NOT EXISTS idx_scripts_created_by ON scripts(created_by);
      CREATE INDEX IF NOT EXISTS idx_scripts_tags ON scripts USING GIN(tags);
      CREATE INDEX IF NOT EXISTS idx_versions_script_id ON versions(script_id);
      CREATE INDEX IF NOT EXISTS idx_versions_content_hash ON versions(content_hash);
      CREATE INDEX IF NOT EXISTS idx_executions_script_id ON executions(script_id);
      CREATE INDEX IF NOT EXISTS idx_executions_connection_id ON executions(connection_id);
      CREATE INDEX IF NOT EXISTS idx_executions_status ON executions(status);
      CREATE INDEX IF NOT EXISTS idx_executions_start_time ON executions(start_time);
      CREATE INDEX IF NOT EXISTS idx_executions_executed_by ON executions(executed_by);
    `);

    // 提交事务
    await client.query('COMMIT');
    console.log('✅ 数据库表创建成功');

  } catch (error) {
    // 回滚事务
    await client.query('ROLLBACK');
    console.error('❌ 创建数据库表失败:', error);
    throw error;
  } finally {
    client.release();
  }
}

async function insertSampleData() {
  console.log('📝 插入示例数据...');

  const client = await pool.connect();
  
  try {
    // 插入根文件夹
    const rootFolderResult = await client.query(`
      INSERT INTO folders (name, parent_id, created_by) 
      VALUES ('示例文件夹', NULL, 'system')
      ON CONFLICT (name, parent_id) DO NOTHING
      RETURNING id
    `);

    let folderId = null;
    if (rootFolderResult.rows.length > 0) {
      folderId = rootFolderResult.rows[0].id;
    } else {
      // 如果已存在，获取ID
      const existingFolder = await client.query(`
        SELECT id FROM folders WHERE name = '示例文件夹' AND parent_id IS NULL
      `);
      if (existingFolder.rows.length > 0) {
        folderId = existingFolder.rows[0].id;
      }
    }

    // 插入示例脚本
    if (folderId) {
      await client.query(`
        INSERT INTO scripts (name, content, description, folder_id, tags, created_by) 
        VALUES (
          '示例查询脚本', 
          'SELECT COUNT(*) as total_records FROM your_table;',
          '这是一个示例查询脚本，用于统计表中的记录数',
          $1,
          ARRAY['示例', '查询', '统计'],
          'system'
        )
        ON CONFLICT (name, folder_id) DO NOTHING
      `, [folderId]);
    }

    // 插入示例连接（注意：这里使用简单加密，生产环境需要更安全的加密方式）
    await client.query(`
      INSERT INTO connections (name, host, port, username, password_encrypted, database_name, created_by) 
      VALUES (
        '本地Doris连接', 
        'localhost', 
        9030, 
        'root', 
        'encrypted_password_placeholder',
        'demo_db',
        'system'
      )
      ON CONFLICT (name) DO NOTHING
    `);

    console.log('✅ 示例数据插入成功');

  } catch (error) {
    console.error('❌ 插入示例数据失败:', error);
    throw error;
  } finally {
    client.release();
  }
}

async function initializeDatabase() {
  console.log('🚀 开始数据库初始化...\n');

  try {
    // 1. 测试数据库连接
    console.log('🔌 测试数据库连接...');
    const client = await pool.connect();
    const result = await client.query('SELECT NOW() as current_time');
    console.log('✅ PostgreSQL 连接成功:', result.rows[0]);
    client.release();

    // 2. 测试 Redis 连接
    const redisConnected = await testRedisConnection();
    if (!redisConnected) {
      throw new Error('Redis 连接失败');
    }

    // 3. 创建数据库表
    await createTables();

    // 4. 插入示例数据
    await insertSampleData();

    console.log('\n🎉 数据库初始化完成！');
    console.log('\n📋 创建的表:');
    console.log('   ✅ folders - 文件夹表');
    console.log('   ✅ scripts - 脚本表');
    console.log('   ✅ versions - 版本表');
    console.log('   ✅ connections - 数据库连接表');
    console.log('   ✅ executions - 执行记录表');
    console.log('\n📊 插入的示例数据:');
    console.log('   ✅ 示例文件夹');
    console.log('   ✅ 示例查询脚本');
    console.log('   ✅ 本地Doris连接');

  } catch (error) {
    console.error('\n💥 数据库初始化失败:', error);
    process.exit(1);
  } finally {
    // 关闭连接池
    await pool.end();
    console.log('\n🔌 数据库连接已关闭');
  }
}

// 运行初始化
if (require.main === module) {
  initializeDatabase().catch(console.error);
}

export { initializeDatabase };